package com.module.codeBind.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import base.common.CUDEnum;
import base.dao.GenericHibernateDao;
import base.util.Page;
import base.util.StringUtil;

import com.module.codeBind.vo.BatchCodeItem;
import com.module.codeBind.vo.CodeApp;


public class CodeAppDao extends GenericHibernateDao<CodeApp,String>{
    static CodeAppDao dao ;
    CodeAppDao(){

    }
    public static CodeAppDao getInstance(){
        if(dao== null){
            dao = new CodeAppDao();
        }
        return dao;
    }
    
    /**
	 * 分页查询所有
	 * @param bean 码应用对象
	 * @param start 开始行
	 * @param limit 每一页的最大行数
	 * @return Page 分页对象
	 */
	@SuppressWarnings("unchecked")
	public Page toList(Session session,CodeApp bean,Integer start,Integer limit){
		Page page = new Page();
		page.setStart(start);
		page.setLimit(limit);
    	String sql=" from CodeApp  where status !='D'";
    	String contSql="select count(*) from CodeApp   where status !='D'";
    	StringBuffer sb = new StringBuffer(sql);
		StringBuffer condition = new StringBuffer();
		if(!StringUtil.isNull(bean.getAppCode())){
			condition.append(" and appCode like '%"+bean.getAppCode()+"%'");
		}
		if(!StringUtil.isNull(bean.getName())){
			condition.append(" and name like '%"+bean.getName()+"%'");
		}
		sb.append(condition.append(" ORDER BY createTime desc"));
		Query query = session.createQuery(sb.toString());
		query.setFirstResult(start).setMaxResults(limit);
		Query totalQuery = session.createQuery(contSql+ condition);
		page.setTotal((long) ((Long) totalQuery.uniqueResult()).intValue());	
		page.setRows(query.list());
		return page;
    }
	
	/**
	 * 逻辑删除
	 * @param obj bean对象
	 * @return 返回成功或者失败
	 */
    public boolean delete(Session session,CodeApp obj){
        boolean flag = false;
        try {
            String hql = "update CodeApp set updaterId = ?,updateTime=?,status =? where id in ("+obj.getId()+")";
            Query query  = session.createQuery(hql); 
                  query.setParameter(0,obj.getUpdaterId());
                  query.setParameter(1, new Date());
                  query.setParameter(2, CUDEnum.D.toString());
                  query.executeUpdate();
            flag = true;
            return flag;
        } catch (Exception ex) {
            ex.printStackTrace();
            return flag;
        }
    }
    /**
     * 不分页查询所有码应用
     * @return 所有码应用结果集
     */
    @SuppressWarnings("unchecked")
	public List<CodeApp> readAll(Session session)
    {
    	Query query = session.createQuery(" from CodeApp  where status !='D'");
        return query.list();
    }
    /**
     * 根据绑定查询应用编号
     * @param bindid 绑定ID
     * @return
     */
    @SuppressWarnings("unchecked")
	public List<String> getAppCodeByBindid(Session session,String bindid){
    	Query query = session.createSQLQuery("select c_codeApp_code from endw_codeapp  where c_status !='D' " +
    			"and c_codeApp_id in (select c_codeapp_id  from endw_bindapp where c_bind_id = '"+bindid+"') ");
        return query.list();
    }
    /**
     * 查询绑定记录的码应用
     * @param bindId 码绑定ID
     * @return 返回对应的码应用结果集
     */
    @SuppressWarnings("unchecked")
	public List<CodeApp> getBindCodeApp(Session session,String bindId){
    	String SQLStr = "select c_code_app_id  from endw_bind where c_bind_id = '"+bindId+"'";	
    	List<String> codeAppId = session.createSQLQuery(SQLStr).list();
    	String HQLStr = "from CodeApp  where c_codeApp_id in("+StringUtil.getNewStrByList(codeAppId)+")";
    	Query query = session.createQuery(HQLStr);
    	return query.list();   	
    }
    /**
     * 根据码应用编号查询数量
     * @param appCode 码应用编号
     * @return 返回总数量
     */
    public long getAppBycode(Session session,String appCode){
    	String hql=" select count(*) from CodeApp  where status !='D' and appCode = ?";
    	Query query  = session.createQuery(hql).setString(0, appCode); 
    	return (long) ((Long) query.uniqueResult()).intValue();
    }
    public List getCodeAppByOrgAppIdList(Session session,List orgAppIdList){
    	String HQLStr = " from CodeApp where id in( "+StringUtil.getNewStrByList(orgAppIdList)+")";	
    	return session.createQuery(HQLStr).list();
    }
    public List<CodeApp> getCodeAppListByBatchCodeItem(Connection conn,BatchCodeItem item) throws SQLException{
    	List<CodeApp> list = new ArrayList<CodeApp>();
    	StringBuffer sb = new StringBuffer("select ec.c_codeApp_id,ec.c_codeApp_name,c_codeApp_code from endw_codeapp ec ");
    	sb.append(" join endw_bind eb on ec.c_codeApp_id = eb.c_code_app_id ");
    	sb.append(" left join endw_bind_batch_code ebbc");
    	sb.append(" on eb.c_bind_id = ebbc.c_bind_id  join endw_batch_code ebc on ebbc.c_batch_code_id = ebc.c_id");
    	sb.append(" join endw_batch_code_item ebci on ebci.c_batch_code_id = ebc.c_id ");
    	sb.append("  where eb.c_status !='D' and ebc.c_status !='D' and ebci.c_volume_no = '").append(item.getVolumeNo()).append("' ");
    	sb.append(" and ebci.i_start_seq<=").append(item.getStartSeq());
    	sb.append(" and ebci.i_end_seq>=").append(item.getEndSeq());
    	Statement stmt = null;
		ResultSet rs = null;
		System.err.println(sb.toString());
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sb.toString());
			while(rs.next()){
				CodeApp bean = new CodeApp();
				bean.setId(rs.getString("c_codeApp_id"));
				bean.setName(rs.getString("c_codeApp_name"));
				bean.setAppCode(rs.getString("c_codeApp_code"));
				list.add(bean);
			}
		}finally{
			close(rs);
			close(stmt);
		}
		return list;
    }
}
